package com.yj.web.dao.mapper;

import com.yj.web.dao.po.admin.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Set;

@Component
public interface FundraisingProjectMapper {
    /**
     *
     * @param statuses considering using a list
     * @param borrower_name
     * @param startTime
     * @param endTime
     * @return
     */
//    @Select("<script>" +
//            "select distinct id, create_id as borrower_id, borrower_name, borrower_id_card, amount, repayment_date, created_at, status from cus_fundraising_project " +
//            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
//            "<if test = \"borrower_name != ''\">" +
//            "instr(borrower_name, #{borrower_name}) " +
//            "</if>" +
//            "<if test = \"statuses != null\">" +
//            "and status in " +
//            "<foreach item=\"status\" index=\"index\" collection=\"statuses\" \n" +
//            "       open=\"(\" separator=\",\" close=\")\">\n" +
//            "       #{status}" +
//            "</foreach> " +
//            "</if>" +
//            "<if test = \"startTime != ''\">" +
//            " and created_at &gt; #{startTime} " +
//            "</if>" +
//            "<if test = \"endTime != ''\">" +
//            " and created_at &lt; #{endTime} " +
//            "</if>" +
//            "<if test = \"borrower_name != '-1'\">" +
//            " and deleted_flag = 'N' " +
//            "</if>" +
//            "</trim>" +
//            "order by created_at desc " +
//            "</script>")
//    public List<FundraisingProject> listFundraisingProjectByBorrNameCreaTime(@Param("statuses") List<Integer> statuses, @Param("borrower_name") String borrower_name, @Param("startTime") String startTime, @Param("endTime") String endTime);
//
//    //borrower_id, borrower_phone
//    @Select("<script>" +
//            "select distinct cfp.id, cu.id as borrower_id, user_name as borrower_phone from cus_user as cu, cus_fundraising_project as cfp " +
//            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
//            "<if test = \"ids != null\">" +
//            "cu.id in " +
//            "<foreach item=\"id\" index=\"index\" collection=\"ids\" \n" +
//            "       open=\"(\" separator=\",\" close=\")\">\n" +
//            "       #{id}" +
//            "</foreach> " +
//            "</if>" +
//            "<if test = \"borrower_phone != ''\">" +
//            "and instr(user_name, #{borrower_phone}) " +
//            "</if>" +
//            "<if test = \"borrower_phone != '-1'\">" +
//            "and cu.deleted_flag = 'N' and cfp.create_id = cu.id and cfp.deleted_flag = 'N'" +
//            "</if>" +
//            "</trim>" +
//            "limit #{size} offset #{offset}" +
//            "</script>")
//    public List<FundraisingProject> listFundraisingProjectByBorrPhone(@Param("borrower_phone") String borrower_phone, @Param("ids") Set<String> ids, @Param("offset") int offset, @Param("size") int size);
//
//    @Select("<script>" +
//            "select count(cfp.id) from cus_user as cu, cus_fundraising_project as cfp " +
//            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
//            "<if test = \"ids != null\">" +
//            "cu.id in " +
//            "<foreach item=\"id\" index=\"index\" collection=\"ids\" \n" +
//            "       open=\"(\" separator=\",\" close=\")\">\n" +
//            "       #{id}" +
//            "</foreach> " +
//            "</if>" +
//            "<if test = \"borrower_phone != ''\">" +
//            "and instr(user_name, #{borrower_phone}) " +
//            "</if>" +
//            "<if test = \"borrower_phone != '-1'\">" +
//            "and cu.deleted_flag = 'N' and cfp.create_id = cu.id and cfp.deleted_flag = 'N'" +
//            "</if>" +
//            "</trim>" +
//            "</script>")
//    public int countFundraisingProject(@Param("borrower_phone") String borrower_phone, @Param("ids") Set<String> ids);

    //*******************************************

    @Select("<script>" +
            "select distinct cfp.id, cu.user_name as borrower_phone, cfp.create_id as borrower_id, cfp.borrower_name, cfp.borrower_id_card, cfp.amount/100 as amount, cfp.cur_amount/100 as cur_amount, cfp.repayment_date, cfp.created_at, cfp.status, cfp.hide_flag, cfp.title, cu.nick_name " +
            "from cus_fundraising_project as cfp join cus_user as cu on cfp.create_id = cu.id " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"borrower_name != ''\">" +
            "instr(borrower_name, #{borrower_name}) " +
            "</if>" +
            "<if test = \"statuses != null\">" +
            "and cfp.status in " +
            "<foreach item=\"status\" index=\"index\" collection=\"statuses\" \n" +
            "       open=\"(\" separator=\",\" close=\")\">\n" +
            "       #{status}" +
            "</foreach> " +
            "</if>" +
            "<if test = \"startTime != ''\">" +
            " and cfp.created_at &gt; #{startTime} " +
            "</if>" +
            "<if test = \"endTime != ''\">" +
            " and cfp.created_at &lt; #{endTime} " +
            "</if>" +
            "<if test = \"borrower_phone != ''\">" +
            "and instr(user_name, #{borrower_phone}) " +
            "</if>" +
            "<if test = \"borrower_phone != '-1'\">" +
            "and cu.deleted_flag = 'N' and cfp.deleted_flag = 'N'" +
            "</if>" +
            "</trim>" +
            "order by created_at desc limit #{size} offset #{offset}" +
            "</script>")
    public List<FundraisingProject> listFundraisingProject(@Param("statuses") List<Integer> statuses, @Param("borrower_name") String borrower_name, @Param("startTime") String startTime, @Param("endTime") String endTime,
                                                                             @Param("borrower_phone") String borrower_phone, @Param("offset") int offset, @Param("size") int size);

    @Select("<script>" +
            "select count(cfp.id) from cus_fundraising_project as cfp, cus_user as cu " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test = \"borrower_name != ''\">" +
            "instr(borrower_name, #{borrower_name}) " +
            "</if>" +
            "<if test = \"statuses != null\">" +
            "and cfp.status in " +
            "<foreach item=\"status\" index=\"index\" collection=\"statuses\" \n" +
            "       open=\"(\" separator=\",\" close=\")\">\n" +
            "       #{status}" +
            "</foreach> " +
            "</if>" +
            "<if test = \"startTime != ''\">" +
            " and cfp.created_at &gt; #{startTime} " +
            "</if>" +
            "<if test = \"endTime != ''\">" +
            " and cfp.created_at &lt; #{endTime} " +
            "</if>" +
            "<if test = \"borrower_phone != ''\">" +
            "and instr(cu.user_name, #{borrower_phone}) " +
            "</if>" +
            "<if test = \"borrower_phone != '-1'\">" +
            "and cu.deleted_flag = 'N' and cfp.create_id = cu.id and cfp.deleted_flag = 'N'" +
            "</if>" +
            "</trim>" +
            "</script>")
    public int countFundraisingProject(@Param("statuses") List<Integer> statuses, @Param("borrower_name") String borrower_name, @Param("startTime") String startTime, @Param("endTime") String endTime,
                                                           @Param("borrower_phone") String borrower_phone);


    //***********************************************

    //项目详情
    @Select("select cfp.id, cfp.create_id as borrower_id, cfp.amount/100 as amount, cfp.cur_amount/100 as cur_amount, cfp.periods, cfp.annual_rate, cfp.repayment_date, cfp.fund_total_days, cfp.created_at," +
            "cfp.borrower_name, cu.user_name as borrower_phone, cfp.patient_relation, cfp.borrower_id_card, cfp.borrower_id_card_resource_ids, cfp.patient_name, cfp.disease, cfp.title, cfp.reason, cfp.resource_ids, cfp.status " +
            "from cus_fundraising_project as cfp left join cus_user as cu on cfp.create_id = cu.id  where cfp.deleted_flag = 'N' and cfp.id = #{id}")
    public FundraisingProject findFundraisingProjectById(@Param("id") String id);
    //审批意见
    @Select("select * from check_suggestion where project_id = #{projectId} order by create_at desc")
    public List<CheckSuggestion> findCheckSuggestionByProjectId(@Param("projectId") String projectId);
    @Select("select user_name as phone from cus_user where id = #{id}")
    public String findPhoneById(@Param("id") String id);

    //投资记录
    @Select("select cpfd.id as investId, cpfd.lender_user_id as lenderId, cu.nick_name as nickname, cu.user_name as phone, " +
            "cpfd.loan_amount/100 as investment, cpfd.profit_amount/100 as interest, cpfd.created_at as createdAt, cpfd.status as status " +
            "from cus_user as cu, cus_project_fundraising_detail as cpfd " +
            "where cu.deleted_flag = 'N' and cpfd.fund_project_id = #{projectId} and  cpfd.lender_user_id = cu.id and cpfd.deleted_flag = 'N'" +
            "order by cpfd.id asc limit #{size} offset #{offset}")
    public List<InvestRecord> findInvestRecordByProjectId(@Param("projectId") String projectId, @Param("offset") int offset, @Param("size") int size);

    @Select("select count(*) from cus_user as cu, cus_project_fundraising_detail as cpfd " +
            "where cu.deleted_flag = 'N' and cpfd.fund_project_id = #{projectId} and  cpfd.lender_user_id = cu.id and cpfd.deleted_flag = 'N'")
    public int countInvestRecordByProjectId(@Param("projectId") String projectId);

    //以分为单位，不作修改
    @Select("select cpfd.id as investId, cpfd.lender_user_id as lenderId, cu.nick_name as nickname, cu.user_name as phone, " +
            "cpfd.loan_amount as investment, cpfd.profit_amount as interest, cpfd.created_at as createdAt, cpfd.status as status " +
            "from cus_user as cu, cus_project_fundraising_detail as cpfd " +
            "where cu.deleted_flag = 'N' and cpfd.fund_project_id = #{projectId} and  cpfd.lender_user_id = cu.id and cpfd.deleted_flag = 'N' and cpfd.status=0 " +
            "order by cpfd.id asc")
    public List<InvestRecord> findInvestRecordByProjectIdNoPaging(@Param("projectId") String projectId);

    //还款计划
    @Select("select cprp.current_period, cprp.fund_project_id, (cprp.principal_amount + cprp.interest_amount)/100 as repay_amount, cprp.plan_repay_at, cprp.status, cprp.split_status " +
            "from cus_project_repay_plan as cprp, cus_fundraising_project as cfp " +
            "where cprp.deleted_flag = 'N' and cprp.fund_project_id = cfp.id and cfp.id = #{projectId} and cfp.deleted_flag = 'N' " +
            "order by cprp.current_period asc limit #{size} offset #{offset}")
    public List<RepayPlan> findRepayPlanByProjectId(@Param("projectId") String projectId, @Param("offset") int offset, @Param("size") int size);
    //计算剩余未还金额
    @Select("select " +
            "(cfp.cur_amount + cfp.total_interest - sum(cprp.principal_amount) - sum(cprp.interest_amount))/100 as remain_amount " +
            "from cus_project_repay_plan as cprp, cus_fundraising_project as cfp " +
            "where cprp.deleted_flag = 'N' and cprp.fund_project_id = cfp.id and cfp.id = #{projectId} and cfp.deleted_flag = 'N' and cprp.current_period <= #{current_period} ")
    public Double calculateRepayPlanRemainAmount(@Param("projectId") String projectId, @Param("current_period") int current_period);

    @Select("select count(*) from cus_project_repay_plan as cprp, cus_fundraising_project as cfp " +
            "where cprp.deleted_flag = 'N' and cprp.fund_project_id = cfp.id and cfp.id = #{projectId} and cfp.deleted_flag = 'N'" )
    public int countRepayPlanByProjectId(@Param("projectId") String projectId);

    //筹款中才能终止项目
    @Update("update cus_fundraising_project set status = 5 where id = #{id} and status in (3,2) and deleted_flag = 'N'")
    public boolean terminateProject(@Param("id") String id);

    //确认放款，更新项目表的总利息
    @Update("update cus_fundraising_project set status = 11, total_interest = #{total_interest}, repayment_date = #{repayment_date} where id = #{id} and status in (7,9) and deleted_flag = 'N'")
    public boolean confirmLoan(@Param("id") String id, @Param("total_interest") long total_interest, @Param("repayment_date") String repayment_date);

//    @Select("select amount/100 from cus_fundraising_project where id = #{id} and deleted_flag = 'N'")
//    public double findProjectAmountById(@Param("id") String id);

    @Select("select cur_amount from cus_fundraising_project where id = #{id} and deleted_flag = 'N'")
    public int findProjectCur_amountById(@Param("id") String id);//暂时以分为单位

    //find RepayPlan By Project Id and Period 以分为单位
    @Select("select *, (cprp.principal_amount + cprp.interest_amount)/100 as repay_amount from cus_project_repay_plan as cprp, cus_fundraising_project as cfp " +
            "where cprp.deleted_flag = 'N' and cprp.fund_project_id = cfp.id and cfp.id = #{projectId} and cfp.deleted_flag = 'N' and cprp.current_period = #{period}")
    public RepayPlan findRepayPlanByProjIdPeriod(@Param("projectId") String projectId, @Param("period") int period);

    //update repay plan
    @Update("update cus_project_repay_plan set updated_at = now(), plan_repay_at = #{repayPlan.plan_repay_at}, principal_amount = #{repayPlan.principal_amount}, interest_amount = #{repayPlan.interest_amount}, split_status = #{repayPlan.split_status} " +
            "where fund_project_id = #{repayPlan.fund_project_id} and current_period = #{repayPlan.current_period}")
    public boolean updateRepayPlan(@Param("repayPlan") RepayPlan repayPlan);

//    @Update("update cus_fundraising_project set repayment_date = #{plan_repay_at} where id = #{id}")
//    public boolean updateProjectPlanRepayAt(@Param("id") String id, @Param("plan_repay_at") String plan_repay_at);

    //项目回款计划
    @Select("select * from cus_project_return_plan where fund_project_id = #{projectId} and current_period = #{current_period} and lender_user_id = #{lender_user_id} and project_fund_detail_id = #{investId}")
    public ReturnPlan findReturnPlanByProjectIdPeriodUserid(@Param("projectId") String projectId, @Param("current_period") int current_period, @Param("lender_user_id") String lender_user_id, @Param("investId") String investId);
    @Select("select cprp.id, cprp.current_period, cprp.fund_project_id, cprp.lender_user_id, cprp.project_fund_detail_id, cprp.plan_return_at, cprp.real_return_at, cprp.principal_amount, cprp.interest_amount, cprp.status from cus_project_return_plan as cprp join cus_project_fundraising_detail as cpfd on cprp.project_fund_detail_id = cpfd.id where cprp.fund_project_id = #{projectId} and cprp.current_period = #{current_period} and cpfd.status = 1")
    public List<ReturnPlan> findReturnPlanByProjectIdPeriod(@Param("projectId") String projectId, @Param("current_period") int current_period);//以分为单位
    //update cus_project_return_plan
    @Update("update cus_project_return_plan set updated_at = now(), plan_return_at = #{returnPlan.plan_return_at}, " +
            "principal_amount = #{returnPlan.principal_amount}, interest_amount = #{returnPlan.interest_amount}, " +
            "status = #{returnPlan.status}, real_return_at = #{returnPlan.real_return_at} " +
            "where id = #{returnPlan.id}")
    public boolean updateReturnPlan(@Param("returnPlan") ReturnPlan returnPlan);
    @Select("select sum(principal_amount) from cus_project_return_plan where fund_project_id = #{projectId} and lender_user_id = #{lender_user_id} and project_fund_detail_id = #{project_fund_detail_id} and status = 2")
    public int sumRepaymentAmount(@Param("projectId") String projectId, @Param("lender_user_id") String lender_user_id, @Param("project_fund_detail_id") String project_fund_detail_id);
    @Select("select sum(interest_amount) from cus_project_return_plan where fund_project_id = #{projectId} and lender_user_id = #{lender_user_id} and project_fund_detail_id = #{project_fund_detail_id} and status = 2")
    public int sumRepaymentProfitAmount(@Param("projectId") String projectId, @Param("lender_user_id") String lender_user_id, @Param("project_fund_detail_id") String project_fund_detail_id);
    @Update("update cus_project_fundraising_detail set updated_at = now(), repayment_amount = #{repayment_amount}, repayment_profit_amount = #{repayment_profit_amount} " +
            "where fund_project_id = #{projectId} and lender_user_id = #{lender_user_id} and id = #{project_fund_detail_id}")
    public boolean updateProjectFundraisingDetail(@Param("projectId") String projectId, @Param("lender_user_id") String lender_user_id, @Param("project_fund_detail_id") String project_fund_detail_id, @Param("repayment_amount") int repayment_amount, @Param("repayment_profit_amount") int repayment_profit_amount);
    @Update("update cus_project_fundraising_detail set status = 3, real_refund_at = now() where fund_project_id = #{projectId} and status = 0")
    public boolean updateProjectFundraisingDetailTerminateProject(@Param("projectId") String projectId);
    @Update("update cus_project_fundraising_detail set status = 2 where fund_project_id = #{projectId} and status = 1")
    public boolean updateProjectFundraisingDetailStatusFinish(@Param("projectId") String projectId);
    @Update("update cus_project_fundraising_detail set status = 1 where fund_project_id = #{projectId} and status = 0")
    public boolean updateProjectFundraisingDetailStatusConfirmLoan(@Param("projectId") String projectId);
    //审核意见
    @Insert("insert into check_suggestion (project_id, create_at, check_suggestion, status) values (#{checkSuggestion.project_id}, now(), #{checkSuggestion.check_suggestion}, #{checkSuggestion.status})")
    public int insertCheckSuggestion(@Param("checkSuggestion") CheckSuggestion checkSuggestion);
    @Update("update cus_fundraising_project set check_suggestion = #{checkSuggestion.check_suggestion} where id = #{checkSuggestion.project_id} and deleted_flag = 'N'")
    public boolean updateFundraisingProjectSuggestion(@Param("checkSuggestion") CheckSuggestion checkSuggestion);
    //通过审核，更新项目状态,
    @Update("update cus_fundraising_project set status = 3, start_at = #{start_at}, end_at = #{end_at} where id = #{id} and status in (1,2) and deleted_flag = 'N'")
    public boolean updateFundraisingProjectStatusApprove(@Param("id") String id, @Param("start_at") String start_at, @Param("end_at") String end_at);
    @Update("update cus_fundraising_project set status = 2 where id = #{id} and status = 1 and deleted_flag = 'N'")
    public boolean updateFundraisingProjectStatusDeny(@Param("id") String id);
    //用户实名认证
    @Update("update cus_user set rn_auth = 1 where id = #{id} and rn_auth = 0 and deleted_flag = 'N'")
    public boolean updateUserRealNameAuth(@Param("id") String id);
    @Update("update cus_user_info as cui, cus_user as cu set real_name = #{real_name}, idcard_no = #{idcard_no} where cu.id = #{userId} and cu.user_info_id = cui.id and cu.deleted_flag = 'N' and cui.deleted_flag = 'N'")
    public boolean updateCusUserInfo(@Param("userId") String userId, @Param("real_name") String real_name, @Param("idcard_no") String idcard_no);
    //图片地址
    @Select("select sys_path from user_file where id = #{pictureId} and deleted_flag = 'N'")
    public String findPicPath(@Param("pictureId") String pictureId);

    //当日还款
    @Select("select fund_project_id from cus_project_repay_plan as cprp, cus_fundraising_project as cfp " +
            "where cprp.deleted_flag = 'N' and cfp.deleted_flag = 'N' and cprp.fund_project_id = cfp.id " +
            "and cprp.plan_repay_at >= #{start} and cprp.plan_repay_at <= #{end} and cprp.status = 1")
    public Set<String> repayTodayUnpaid(@Param("start") String start, @Param("end") String end);

    @Select("select cprp.current_period, cprp.fund_project_id, (cprp.principal_amount + cprp.interest_amount)/100 as repay_amount, cprp.plan_repay_at, cprp.status, cprp.split_status, principal_amount/100 as principal_amount, interest_amount/100 as interest_amount " +
            "from cus_project_repay_plan as cprp, cus_fundraising_project as cfp " +
            "where cprp.deleted_flag = 'N' and cfp.deleted_flag = 'N' and cprp.fund_project_id = cfp.id " +
            "and cprp.plan_repay_at >= #{start} and cprp.plan_repay_at <= #{end} and cprp.status = 1")
    public Set<RepayPlan> repayTodayUnpaid2(@Param("start") String start, @Param("end") String end);

    //逾期的还款计划
    @Select("select cprp.current_period, cprp.fund_project_id, (cprp.principal_amount + cprp.interest_amount)/100 as repay_amount, cprp.plan_repay_at, cprp.status, cprp.split_status, principal_amount/100 as principal_amount, interest_amount/100 as interest_amount " +
            "from cus_project_repay_plan as cprp left join cus_fundraising_project as cfp on cprp.fund_project_id = cfp.id " +
            "where cprp.deleted_flag = 'N' and cfp.deleted_flag = 'N' " +
            "and cprp.plan_repay_at < #{today} and cprp.status in (1,3)")
    public Set<RepayPlan> repayOverdue(@Param("today") String today);
    //隐藏
    @Update("update cus_fundraising_project set hide_flag = 1 where id = #{id} and hide_flag = 0")
    public boolean hideProject(@Param("id") String id);
    @Update("update cus_fundraising_project set hide_flag = 0 where id = #{id} and hide_flag = 1")
    public boolean displayProject(@Param("id") String id);


    //cus_fund_project_tips
    @Insert("insert into cus_fund_project_tips (id, user_id, created_at, title, msg_body, fund_project_id, project_type, msg_type) values (#{projectTip.id}, #{projectTip.user_id}, now(), #{projectTip.title}, #{projectTip.msg_body}, #{projectTip.fund_project_id}, #{projectTip.project_type}, #{projectTip.msg_type})")
    public int insertProjectTip(@Param("projectTip") ProjectTip projectTip);

    @Select("select periods from cus_fundraising_project where id = #{projectId} and deleted_flag = 'N'")
    public int findPeriodByProjectId(@Param("projectId") String projectId);

    @Update("update cus_fundraising_project set status = 15 where id = #{projectId} and status in (11,13) and deleted_flag = 'N'")
    public boolean updateProjectStatusFinish(@Param("projectId") String projectId);

    @Select("select fund_project_id from cus_project_repay_plan where status = 2 and split_status = 0")
    public Set<String> findRepayPlanPayedUnsplit();
}
